package com.aofeng.hybrid.orm;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import org.json.JSONArray;
import org.json.JSONObject;

/**
 * Sql 辅助类
 * Created by LGY on 2016-07-05.
 */
public class SqlHelper {
    /**
     * 执行sql
     * @param entity 实体
     * @param sql sql语句
     * @throws Exception
     */
    public static void run(Context context, String sql) throws Exception {
        SQLiteDatabase db = context.openOrCreateDatabase("hybrid.db", Context.MODE_PRIVATE, null);
        try {
            db.execSQL(sql);
        }finally {
            if(db != null)
                db.close();
        }
    }

    /**
     * 返回查询结果数组
     * @param sql sql
     * @return 行集
     * @throws Exception
     */
    public static JSONArray list(Context context, String sql) throws Exception {
        return pagedList(context, sql, 0, 0);
    }

    /**
     * 返回单行数据
     * @param sql
     * @return 单行
     * @throws Exception
     */
    public static JSONObject singleRow(Context context, String sql) throws Exception {
        JSONObject row = new JSONObject();
        SQLiteDatabase db = context.openOrCreateDatabase("hybrid.db", Context.MODE_PRIVATE, null);
        try {
            Cursor cursor = db.rawQuery(sql, new String[]{});
            if(cursor.moveToNext()) {
                for(int i=0; i<cursor.getColumnCount(); i++) {
                    row.put(cursor.getColumnName(i), Column.normalizeValue(cursor, i));
                }
            }
            if(cursor.moveToNext()) throw new Exception("返回超过一条结果。");
            return row;
        }finally {
            if(db != null)
                db.close();
        }
    }

    /**
     * 分页查询
     * @param sql sql
     * @param pageNo 页号
     * @param pageSize 页行数
     * @return 数组
     * @throws Exception
     */
    public static JSONArray pagedList(Context context, String sql, int pageNo, int pageSize) throws Exception {
        JSONArray array = new JSONArray();
        SQLiteDatabase db = context.openOrCreateDatabase("hybrid.db", Context.MODE_PRIVATE, null);
        try {
            if(pageSize >= 0)
                sql += " LIMIT " + pageSize + " " + (pageNo * pageSize);
            Cursor cursor = db.rawQuery(sql, new String[]{});
            while(cursor.moveToNext()) {
                JSONObject row = new JSONObject();
                for(int i=0; i<cursor.getColumnCount(); i++) {
                    row.put(cursor.getColumnName(i), Column.normalizeValue(cursor, i));
                }
                array.put(row);
            }
            return array;
        }finally {
            if(db != null)
                db.close();
        }
    }

}
